package com.org.core.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.org.core.annotations.Identify;
import com.org.core.annotations.Transient;
import com.org.core.constant.CommonConstant;
import com.org.core.constant.Pager;
import com.org.core.exception.AnnotationException;

public class SqlUtil {

	
	public static String createUpdateSqlBy(Class<?> clazz, String tableName) {
        String sqlReturn = null;
        String fieldName = null;
        String columnName = null;
        
        Field[] fields = clazz.getDeclaredFields();
        if(fields != null && fields.length> 0) {
        	// 预处理fields, 把自增长的id去掉不处理
        	List<Field> fieldList = removeIdentifyField(fields);
        	try {
        		StringBuffer sql = new StringBuffer("update ");
        		sql.append(tableName).append(" set ");
        		for (int i = 0; i < fieldList.size(); i++) {
        			fieldName = fieldList.get(i).getName();
        			// 列名
        			columnName = StringUtil.toDbName(fieldName);
        			sql.append(columnName).append("=?,");
        		}
        		// 移除最后一个 逗号
        		sql = StringUtil.removeLastChar(sql);
        		
        		sql.append(" where id=?");
        		sqlReturn = sql.toString();
        	} catch (SecurityException e) {
        		e.printStackTrace();
        	} catch (IllegalArgumentException e) {
        		e.printStackTrace();
        	}
        }
		return sqlReturn;
	}
	
	public static String createInsertSqlBy(Class<?> clazz, String tableName) {
		String sqlReturn = null;
		String fieldName = null;
		String columnName = null;
		
		Field[] fields = clazz.getDeclaredFields();
		if(fields != null && fields.length> 0) {
			// 预处理fields, 把自增长的id去掉不处理
			List<Field> fieldList = removeIdentifyField(fields);
			try {
				StringBuffer sql = new StringBuffer("insert into ");
				sql.append(tableName).append(" (");
				for (int i = 0; i < fieldList.size(); i++) {
					fieldName = fieldList.get(i).getName();
					// 列名
					columnName = StringUtil.toDbName(fieldName);
					sql.append(columnName).append(",");
				}
				// 移除最后一个 逗号
				sql = StringUtil.removeLastChar(sql);
				
				sql.append(") values (");
				
				for (int i = 0; i < fieldList.size(); i++) {
					sql.append("?,");
				}
				// 再移除最后一个 逗号
				sql = StringUtil.removeLastChar(sql);
				
				sql.append(")");
				
				sqlReturn = sql.toString();
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
		}
		return sqlReturn;
	}

	/**
	 * 根据pager对象组建查询。
	 * @param tableName
	 * @param pager
	 * @return
	 */
	public static String createSelectSqlByPager(String tableName, Pager pager) {
		if(StringUtils.isBlank(tableName)) {
			throw new AnnotationException("创建查询sql失败: tableName不能为空");
		}
        String sqlReturn = null;
        try {
    		StringBuffer sql = new StringBuffer("select * from ");
    		sql.append(tableName);
    		
    		String orderBy = null;
    		String sc = null;
			if(pager != null) {
				if(StringUtils.isNotBlank(pager.getOrderBy())) {
					orderBy = pager.getOrderBy();
					sc = pager.getSc();
					if(sc == null || sc.toString().equals("")) {
						throw new AnnotationException("创建查询sql失败: 已指定需要排序的字段"+orderBy+"，未指定排序方式");
					}
				}
				/*
				// 先假设这里没有其他查询条件了
	    		if(params.size() > 0) {
	    			sql.append(" where ");
	    			String key = null;
	    			for (Iterator<?> iterator = params.keySet().iterator(); iterator.hasNext();) {
						key = (String) iterator.next();
						sql.append(key).append(" = ? and ");
					}
	        		// 再移除最后一个and 
	        		sql = StringUtil.removeLastNChar(sql, 4);
	    		}*/
			}
			
    		if(StringUtils.isNotBlank(orderBy)) {
    			sql.append(" order by ").append(orderBy).append(" ").append(sc);
    		}
    		
    		sql.append(" limit ?, ?");
    		
    		sqlReturn = sql.toString();
    	} catch (SecurityException e) {
    		e.printStackTrace();
    	} catch (IllegalArgumentException e) {
    		e.printStackTrace();
    	}
        
		return sqlReturn;
	}
	
	public static String createSelectSqlBy(String tableName, Map<String, Object> params) {
		if(StringUtils.isBlank(tableName)) {
			throw new AnnotationException("创建查询sql失败: tableName不能为空");
		}
		String sqlReturn = null;
		try {
			StringBuffer sql = new StringBuffer("select * from ");
			sql.append(tableName);
			
			Object orderBy = null;
			Object sc = null;
			if(params != null) {
				if(params.containsKey(CommonConstant.ORDER_BY)) {
					orderBy = params.remove(CommonConstant.ORDER_BY);
					sc = params.remove(CommonConstant.SC);
					if(sc == null || sc.toString().equals("")) {
						throw new AnnotationException("创建查询sql失败: 已指定需要排序的字段"+orderBy+"，未指定排序方式");
					}
				}
				
				if(params.size() > 0) {
					sql.append(" where ");
					String key = null;
					for (Iterator<?> iterator = params.keySet().iterator(); iterator.hasNext();) {
						key = (String) iterator.next();
						sql.append(key).append(" = ? and ");
					}
					// 再移除最后一个and 
					sql = StringUtil.removeLastNChar(sql, 4);
				}
			}
			
			if(orderBy != null && !orderBy.toString().equals("")) {
				sql.append(" order by ").append(orderBy).append(" ").append(sc);
			}
			
			sqlReturn = sql.toString();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		
		return sqlReturn;
	}
	
	public static String createDeleteSqlBy(String tableName, Map<String, Object> params) {
		if(StringUtils.isBlank(tableName)) {
			throw new AnnotationException("创建查询sql失败: tableName不能为空");
		}
		String sqlReturn = null;
		try {
			StringBuffer sql = new StringBuffer("delete from ");
			sql.append(tableName);
			
			if(params != null) {
				
				if(params.size() > 0) {
					sql.append(" where ");
					String key = null;
					for (Iterator<?> iterator = params.keySet().iterator(); iterator.hasNext();) {
						key = (String) iterator.next();
						sql.append(key).append(" = ? and ");
					}
					// 再移除最后一个and 
					sql = StringUtil.removeLastNChar(sql, 4);
				}
			}
			
			sqlReturn = sql.toString();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		
		return sqlReturn;
	}
	
	public static Map<Integer, Object> createParamsBy(Object obj) {
		Map<Integer, Object> paramsReturn = null;
        
		Class<?> clazz = obj.getClass();
		Method method = null;
        String fieldName = null;
        Object val = null;
        
        Field[] fields = clazz.getDeclaredFields();
        if(fields != null && fields.length> 0) {
        	// 预处理fields, 把自增长的id去掉不处理
        	List<Field> fieldList = removeIdentifyField(fields);
	        try {
	        	paramsReturn = new HashMap<Integer, Object>();
	    		for (int i = 0; i < fieldList.size(); i++) {
	    			fieldName = fieldList.get(i).getName();
	    	        method = clazz.getDeclaredMethod(StringUtil.toGetter(fieldName));
	    	        // 参数值
	    	        val = method.invoke(obj);
	    	        paramsReturn.put(i+1, val);
	    		}
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}
        }
		return paramsReturn;
	}
	
	public static Map<Integer, Object> createUpdateParamsBy(Object obj) {
		Map<Integer, Object> paramsReturn = null;
        
		Class<?> clazz = obj.getClass();
		Method method = null;
        String fieldName = null;
        Object val = null;
        
        List<Field> fieldList = removeFieldForUpdateSql(clazz.getDeclaredFields());
        if(fieldList != null && fieldList.size() > 0) {
	        try {
	        	paramsReturn = new HashMap<Integer, Object>();
	        	Object idValue = null;
	    		for (int i = 0; i < fieldList.size(); i++) {
	    			fieldName = fieldList.get(i).getName();
	    	        method = clazz.getDeclaredMethod(StringUtil.toGetter(fieldName));
	    	        // 参数值
	    	        val = method.invoke(obj);
	    	        if(fieldList.get(i).getAnnotation(Identify.class) != null && val != null && val != "") {
	    				idValue = val;
	    			} else {
	    				paramsReturn.put(paramsReturn.size() + 1, val);
	    			}
	    		}
	    		paramsReturn.put(paramsReturn.size()+1, idValue);
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				e.printStackTrace();
			}
        }
		return paramsReturn;
	}
	
	private static List<Field> removeIdentifyField(Field[] fields){
		List<Field> newFields = new ArrayList<Field>();
		for (int i = 0; i < fields.length; i++) {
			if(fields[i].getAnnotation(Identify.class) == null 
					&& fields[i].getAnnotation(Transient.class) == null
					&& (fields[i].getModifiers() & Modifier.STATIC) != Modifier.STATIC) {
				newFields.add(fields[i]);
			}
		}
		return newFields;
	}
	
	private static List<Field> removeFieldForUpdateSql(Field[] fields){
		List<Field> newFields = new ArrayList<Field>();
		for (int i = 0; i < fields.length; i++) {
			if(fields[i].getAnnotation(Transient.class) == null
					&& (fields[i].getModifiers() & Modifier.STATIC) != Modifier.STATIC) {
				newFields.add(fields[i]);
			}
		}
		return newFields;
	}
}
